from google.appengine.api import rdbms
from django.utils.encoding import smart_str, smart_unicode

_INSTANCE='dublin-nightsdb:instance1'


class DBQuery:

	def __init__(self):
		pass
		
	# search == ("club", "Copper Face Jacks")
	def build_query(self, search):
		if search[0] == 'club':
			querystring = "SELECT events.* FROM events, clubs where events.ClubID = clubs.ClubID and club.Name = '%s' " % (str(search[1]))
		elif search[0] == 'Events':
			querystring = "SELECT * FROM events"
		elif search[0] == 'Genre':
			querystring = "SELECT * FROM events where description like '%%%s%%' " % (str(search[1]))
		elif search[0] == 'Price':
			querystring = "SELECT * FROM events where description like '%%%s%%' " % (smart_str(search[1]))
		else:
			querystring = "SELECT * FROM events where %s='%s'" % (str(search[0]), str(search[1]))
		return querystring

	def query_venue(self):
		# Connect to DB and get Club Data
		conn = rdbms.connect(instance=_INSTANCE, database='mysql')
		cur  = conn.cursor()
		query = "SELECT Name, Address, ContactNo, Email, ClubPicURL FROM clubs"
		cur.execute(query)
		data = cur.fetchall()
		# Return Club data
		if data:
			keys = ["clubName", "clubAddr", "clubNo", "clubMail", "clubPicURL"]
			results = [dict(zip(keys, row)) for row in data]
			return results
		else:
			return "None"
		
	# eg search == ('genre', 'rock')
	def query(self, search):
		conn = rdbms.connect(instance=_INSTANCE, database='mysql')
		cur  = conn.cursor()
		malarkey = self.build_query(search)
		resultset = []
		resultdict = {}
		cur.execute(malarkey)
		results = cur.fetchall()
		
		if cur.rowcount > 0 :
			for row in results:
				resultdict['name'] = row[12]
				resultdict['link'] = row[13]
				resultdict['location'] = row[11]
				resultdict['profile_link'] = row[9]
				resultdict['start_time'] = row[5]
				resultdict['age'] = str(row[2])
				resultdict['price'] = str(row[3])
				resultdict['genre'] = row[10] #Description for now.... No column for genre, and not sure if mosse's script can get it
				if row[8] == 1:
					resultdict['promoted'] = "Promoted"
				elif row[8] == 0:
					resultdict['promoted'] = "" #can change this if necessary
				if row[8] == 1:
					resultdict['VIP'] = "VIP"
				elif row[8] == 0:
					resultdict['VIP'] = "" #can change this if necessary

				resultdict['day'] = row[6]
				resultset.append(resultdict)
				resultdict = {}
				
			return resultset
		else :
			return "None"
#Ok so, the results from the database come back as a list. I have tried to make it into a dictionary so it can more easily be
#accessed when building the page. An example of what the results will literally look like:
#resultset[0] = {'name':"The Twisted Pepper Christmas Market",'link':"https://www.facebook.com/events/495308023843426",'location'		:"The Twisted Pepper",'profile_link':"https://graph.facebook.com/.../picture?type=square",'start_time':"16:00:00",'age':"18",'pr		ice':"5",'genre':"The Twisted Pepper in association.....",'promoted':"Not Promoted",'day':"2012-12-20"}
		
				
		
		# resultset is a list (rows) of dictionaries (columns)

		
		
